/*
 * Powered By [rapid-framework]
 * Web Site: http://www.rapid-framework.org.cn
 * Google Code: http://code.google.com/p/rapid-framework/
 * Since 2008 - 2016
 */

package com.jzwl.xydk.manager.order.dao;

import java.util.List;
import java.util.Map;
import java.util.HashMap;

import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.support.DataAccessUtils;
import org.springframework.stereotype.Repository;

import com.jzwl.common.id.Sequence;
import com.jzwl.common.page.PageObject;
import com.jzwl.system.base.dao.BaseDAO;



@Repository("businessOrderDao")
public class BusinessOrderDao {
	
	@Autowired
	private BaseDAO baseDAO;//dao基类，操作数据库
	
	
	public boolean addBusinessOrder(Map<String, Object> map) {
		
		//自动注入时间戳为ID 酌情修改数据库类型为bigint  int会越界
		map.put("id",  Sequence.nextId());
		
		String sql = "insert into v2_business_order " 
				 + " (id,businessId,userId,startDate,endDate,cityId,demand,phone,remark,createDate,openId) " 
				 + " values "
				 + " (:id,:businessId,:userId,:startDate,:endDate,:cityId,:demand,:phone,:remark,:createDate,:openId)";
		
		return baseDAO.executeNamedCommand(sql, map);
	}
	
	
	public String getColumns() {
		return ""
				+" id as id,"
				+" businessId as businessId,"
				+" userId as userId,"
				+" startDate as startDate,"
				+" endDate as endDate,"
				+" cityId as cityId,"
				+" demand as demand,"
				+" phone as phone,"
				+" remark as remark,"
				+" createDate as createDate,"
				+" openId as openId"
				;
	}
	

	public PageObject queryBusinessOrderList(Map<String,Object> map) {
		// [column]为字符串拼接, {column}为使用占位符. 如username='[username]',偷懒时可以使用字符串拼接 
		// [column] 为PageRequest的属性
		
				String sql="select t.id,t.skillName,b.userName,bo.startDate,bo.endDate,bo.demand,bo.phone,bo.remark,c.cityName from `xiaoka-xydk`.user_skillinfo t"
				+" LEFT JOIN `xiaoka-xydk`.user_basicinfo b ON t.basicId = b.id "
				+" left join `xiaoka-xydk`.business_order bo ON b.id = bo.userId "
				+ " LEFT JOIN `xiaoka`.tg_city c ON bo.cityId = c.id ";
		
		  	if(null !=map.get("businessId") && StringUtils.isNotEmpty(map.get("businessId").toString())){
			  		sql=sql+ " and t.businessId  = " + map.get("businessId") +"";
		  	}
		  	if(null !=map.get("userId") && StringUtils.isNotEmpty(map.get("userId").toString())){
			  		sql=sql+ " and t.userId  = " + map.get("userId") +"";
		  	}
		  	if(null !=map.get("startDate") && StringUtils.isNotEmpty(map.get("startDate").toString())){
			  		sql=sql+ " and t.startDate >= " + map.get("startDateBegin") +"";
			  		sql=sql+ " and t.startDate <= " + map.get("startDateEnd"  ) +"";
		  	}
		  	if(null !=map.get("endDate") && StringUtils.isNotEmpty(map.get("endDate").toString())){
			  		sql=sql+ " and t.endDate >= " + map.get("endDateBegin") +"";
			  		sql=sql+ " and t.endDate <= " + map.get("endDateEnd"  ) +"";
		  	}
		  	if(null !=map.get("cityId") && StringUtils.isNotEmpty(map.get("cityId").toString())){
			  		sql=sql+ " and t.cityId  = " + map.get("cityId") +"";
		  	}
		  	if(null !=map.get("demand") && StringUtils.isNotEmpty(map.get("demand").toString())){
			  		sql=sql+ " and t.demand  = " + map.get("demand") +"";
		  	}
		  	if(null !=map.get("phone") && StringUtils.isNotEmpty(map.get("phone").toString())){
			  		sql=sql+ " and t.phone  = " + map.get("phone") +"";
		  	}
		  	if(null !=map.get("remark") && StringUtils.isNotEmpty(map.get("remark").toString())){
			  		sql=sql+ " and t.remark  = " + map.get("remark") +"";
		  	}
		  	if(null !=map.get("createDate") && StringUtils.isNotEmpty(map.get("createDate").toString())){
			  		sql=sql+ " and t.createDate >= " + map.get("createDateBegin") +"";
			  		sql=sql+ " and t.createDate <= " + map.get("createDateEnd"  ) +"";
		  	}
		  	if(null !=map.get("openId") && StringUtils.isNotEmpty(map.get("openId").toString())){
			  		sql=sql+ " and t.openId  = " + map.get("openId") +"";
		  	}
					
					
		sql=sql+ " order by t.id ";
		
		PageObject po = baseDAO.queryForMPageList(sql, new Object[]{},map);
		
		return po;
	}

	public boolean updateBusinessOrder(Map<String, Object> map) {
		
		String sql ="update v2_business_order set "
				+ " id=:id,businessId=:businessId,userId=:userId,startDate=:startDate,endDate=:endDate,cityId=:cityId,demand=:demand,phone=:phone,remark=:remark,createDate=:createDate,openId=:openId "
				+ " where id=:id";
		
		return  baseDAO.executeNamedCommand(sql, map);
	}

	public boolean deleteBusinessOrder(Map<String, Object> map) {
		
		String sql = "delete from v2_business_order where id in ("+ map.get("id")+ " ) ";

		return baseDAO.executeNamedCommand(sql, map);
	}
	
	
	@SuppressWarnings("unchecked")
	public Map<String,Object> getById(Map<String, Object> map) {
		
		Map<String,Object> resMap = new HashMap<String,Object>();
		
		String sql="select t.id,t.skillName,b.userName,bo.startDate,bo.endDate,bo.demand,bo.phone,bo.remark,c.cityName from `xiaoka-xydk`.user_skillinfo t"
				+" LEFT JOIN `xiaoka-xydk`.user_basicinfo b ON t.basicId = b.id "
				+" left join `xiaoka-xydk`.business_order bo ON b.id = bo.userId "
				+ " LEFT JOIN `xiaoka`.tg_city c ON bo.cityId = c.id "
				+ " where t.id = "+ map.get("id") + "";

		resMap = baseDAO.queryForMap(sql);
		
		return resMap;
	
	}
	

}
